Reporting System Modelling (OLAP)

Reporting System Modelling focuses on designing databases for Online Analytical Processing (OLAP), such as Data Warehouses (e.g., Snowflake, BigQuery, Redshift).

Unlike OLTP systems which are optimized for fast, single-row transactions, OLAP systems are optimized for executing complex analytical queries, massive aggregations, and business intelligence (BI) reporting across millions or billions of rows.

1. The Core Philosophy: Denormalization

While OLTP relies heavily on Normalization to save space and prevent update anomalies, OLAP relies on Denormalization.

2. Dimensional Modelling (The Kimball Approach)

The industry standard for reporting data models is Dimensional Modelling, pioneered by Ralph Kimball. It divides data into two distinct types of tables: Fact Tables and Dimension Tables.

Fact Tables

Fact tables contain the measurable, quantitative data about a business event.

• Examples: Sales_Amount, Discount_Applied, Quantity_Sold.
• Characteristics:
  • They are narrow (few columns) but extremely deep (billions of rows).
  • They consist mostly of numerical metrics and Foreign Keys pointing to Dimension tables.
  • They are highly additive (easy to SUM, AVG, MIN, MAX).

Dimension Tables

Dimension tables contain the descriptive context (the "who, what, where, when, and why") surrounding a Fact.

• Examples: Customer_Name, Store_Location, Product_Category, Date.
• Characteristics:
  • They are wide (many descriptive columns) but shallow (relatively few rows).
  • They are heavily denormalized. (e.g., A Product_Dim table might include the Category_Name and Department_Name directly, rather than linking to separate tables).

3. Schemas in Data Warehousing

The Star Schema (Standard)

The most common OLAP design. A central Fact table is directly surrounded by its Dimension tables, resembling a star.

• Pros: Extremely fast read performance because queries only ever require a single JOIN from the Fact to a Dimension. Very intuitive for BI analysts to use.
• Cons: High data redundancy in the dimension tables.

The Snowflake Schema (Variant)

A Star Schema where the Dimension tables are slightly normalized into sub-dimensions (e.g., a Store_Dim links to a separate City_Dim).

• Pros: Saves a small amount of storage space.
• Cons: Re-introduces the need for multiple complex joins, sacrificing read performance. Generally discouraged in modern, cheap-storage cloud warehouses.

4. Slowly Changing Dimensions (SCD)

A critical concept in reporting is tracking how descriptive data changes over time (e.g., a customer moves to a new city, but we still want their old purchases credited to their old city).

• SCD Type 1 (Overwrite):
  • Overwrites the old data with new data.
  • Result: No historical tracking. Easy to maintain, but you lose the past.
• SCD Type 2 (Add New Row):
  • The most common method. Adds a new row with the updated data and a new Surrogate Key. Uses Valid_From, Valid_To, and Is_Active columns to track history.
  • Result: Perfect historical tracking. Fact tables link to the specific version of the dimension that was active at the time of the event.
• SCD Type 3 (Add New Column):
  • Keeps the Current_Value and adds an Old_Value column to the same row.
  • Result: Only tracks the single most recent change. Rarely used.